package io.github.talelin.latticy.controller.v1;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import io.github.talelin.latticy.model.CUserDO;
import io.github.talelin.latticy.model.OrderDO;
import io.github.talelin.latticy.model.SpuDO;
import io.github.talelin.latticy.model.SkuDO;
import io.github.talelin.latticy.service.CUserService;
import io.github.talelin.latticy.service.SpuService;
import io.github.talelin.latticy.service.SkuService;
import io.github.talelin.latticy.service.UnifiedOrderService;
import io.github.talelin.latticy.service.UserRechargeService;
import io.github.talelin.latticy.vo.UnifyResponseVO;
import io.github.talelin.latticy.vo.PageResponseVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 自动上架控制器
 */
@RestController
@RequestMapping("/v1/auto-listing")
@CrossOrigin(origins = "*")
public class AutoListingController {

    private static final Logger logger = LoggerFactory.getLogger(AutoListingController.class);

    @Autowired
    private CUserService cUserService;

    @Autowired
    private SpuService spuService;

    @Autowired
    private SkuService skuService;
    
    @Autowired
    private UserRechargeService userRechargeService;

    @Autowired
    private UnifiedOrderService unifiedOrderService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取积分自动上架统计信息
     */
    @GetMapping("/stats")
    public Map<String, Object> getAutoListingStats() {
        Map<String, Object> stats = new HashMap<>();
        
        // 获取用户统计
        long totalUsers = cUserService.count();
        stats.put("total_users", totalUsers);
        
        // 使用SQL聚合查询计算用户积分分布（一次查询完成）
        Map<String, Object> pointsStats = getPointsStatsBySql();
        stats.put("positive_balance_count", pointsStats.get("sufficient_points_users"));
        stats.put("negative_balance_count", pointsStats.get("insufficient_points_users"));
        stats.put("zero_balance_count", pointsStats.get("zero_points_users"));
        
        // 获取商品统计
        long totalProducts = spuService.count();
        stats.put("total_products", totalProducts);
        
        // 获取今日自动上架数量（这里简化处理，实际应该记录自动上架日志）
        stats.put("auto_listed_today", 0);
        stats.put("pending_listings", 0);
        
        return stats;
    }

    /**
     * 批量自动上架所有用户商品
     */
    @PostMapping("/batch-auto-listing")
    public UnifyResponseVO<String> batchAutoListing(@RequestBody(required = false) Object request) {
        try {
            // 获取所有用户ID
            List<CUserDO> allUsers = cUserService.list();
            List<Integer> userIds = allUsers.stream()
                    .map(CUserDO::getId)
                    .collect(java.util.stream.Collectors.toList());
            
            // 批量处理自动上架
            userRechargeService.batchAutoListingByPoints(userIds);
            
            return new UnifyResponseVO<>(0, "批量自动上架完成，共处理 " + userIds.size() + " 个用户");
        } catch (Exception e) {
            return new UnifyResponseVO<>(1, "批量自动上架失败: " + e.getMessage());
        }
    }

    /**
     * 获取用户积分信息（用于用户详情）
     */
    @GetMapping("/user/{userId}/balance")
    public Map<String, Object> getUserBalance(@PathVariable Long userId) {
        try {
            Map<String, Object> result = new HashMap<>();
            
            // 查询用户信息
            CUserDO user = cUserService.getById(userId.intValue());
            if (user == null) {
                Map<String, Object> errorResult = new HashMap<>();
                errorResult.put("error", "用户不存在");
                return errorResult;
            }
            
            // 获取用户积分信息
            Integer points = user.getPoints();
            if (points == null) points = 0;
            
            // 查询积分记录统计
            Map<String, Object> pointsStats = getPointsStatsBySql();
            
            // 查询用户商品统计
            QueryWrapper<SpuDO> spuQuery = new QueryWrapper<>();
            spuQuery.eq("user_id", userId.intValue()).isNull("delete_time");
            List<SpuDO> spuList = spuService.list(spuQuery);
            
            int totalProducts = spuList.size();
            int onlineProducts = 0;
            int offlineProducts = 0;
            
            for (SpuDO spu : spuList) {
                QueryWrapper<SkuDO> skuQuery = new QueryWrapper<>();
                skuQuery.eq("spu_id", spu.getId()).isNull("delete_time");
                List<SkuDO> skuList = skuService.list(skuQuery);
                
                for (SkuDO sku : skuList) {
                    if (sku.getOnline() != null && sku.getOnline() == 1) {
                        onlineProducts++;
                    } else {
                        offlineProducts++;
                    }
                }
            }
            
            // 构建返回数据
            result.put("userId", userId);
            result.put("userNickname", user.getNickname());
            result.put("userMobile", user.getMobile());
            result.put("userRealName", user.getRealName());
            result.put("pointsBalance", points);
            result.put("totalEarnedPoints", 0); // 可以从积分记录表查询
            result.put("totalUsedPoints", 0);   // 可以从积分记录表查询
            result.put("productCount", totalProducts);
            result.put("onlineProductCount", onlineProducts);
            result.put("offlineProductCount", offlineProducts);
            
            // 积分状态和建议
            String pointsStatus;
            String suggestedAction;
            if (points >= 1000) {
                pointsStatus = "sufficient";
                int maxListing = Math.min(points / 500, 20);
                suggestedAction = "积分充足，可上架" + maxListing + "个商品（商品价格100-1000元）";
            } else if (points > 0) {
                pointsStatus = "insufficient";
                int maxListing = Math.min(points / 500, 2);
                suggestedAction = "积分一般，可上架" + maxListing + "个商品（商品价格100-1000元）";
            } else {
                pointsStatus = "zero";
                suggestedAction = "积分不足，无法上架商品";
            }
            
            result.put("balanceStatus", pointsStatus);
            result.put("suggestedAction", suggestedAction);
            
            return result;
        } catch (Exception e) {
            logger.error("获取用户积分信息失败", e);
            Map<String, Object> errorResult = new HashMap<>();
            errorResult.put("error", "获取用户积分信息失败: " + e.getMessage());
            return errorResult;
        }
    }

    /**
     * 自动上架指定用户商品
     */
    @PostMapping("/user/{userId}/auto-listing")
    public UnifyResponseVO<String> autoListingUserProducts(@PathVariable Long userId) {
        try {
            // 调用充值服务的自动上架功能
            userRechargeService.autoListingByPoints(userId.intValue());
            return new UnifyResponseVO<>(0, "用户 " + userId + " 的商品自动上架完成");
        } catch (Exception e) {
            return new UnifyResponseVO<>(1, "自动上架失败: " + e.getMessage());
        }
    }

    /**
     * 获取用户余额列表（分页）- 使用SQL聚合查询优化性能
     */
    @GetMapping("/user-balances")
    public PageResponseVO<Map<String, Object>> getUserBalanceList(@RequestParam(required = false) Integer page,
                                            @RequestParam(required = false) Integer count,
                                            @RequestParam(required = false) String search) {
        Map<String, Object> result = new HashMap<>();
        
        int pageNum = page != null ? page : 0;
        int pageSize = count != null ? count : 10;
        int offset = pageNum * pageSize;
        
        try {
            // 使用SQL聚合查询一次性获取所有数据
            String sql = buildUserBalanceQuery(search, pageSize, offset);
            List<Map<String, Object>> items = jdbcTemplate.queryForList(sql);
            
            // 获取总数
            String countSql = buildUserBalanceCountQuery(search);
            Long total = jdbcTemplate.queryForObject(countSql, Long.class);
            
            result.put("total", total != null ? total : 0L);
            result.put("count", (long) pageSize);
            result.put("page", (long) pageNum);
            result.put("items", items);
            
        } catch (Exception e) {
            // 如果SQL查询失败，回退到原来的方法
            System.err.println("SQL查询失败，回退到原方法: " + e.getMessage());
            return getUserBalanceListFallback(page, count, search);
        }
        
        return PageResponseVO.<Map<String, Object>>builder()
                .total(((Number) result.get("total")).longValue())
                .items((List<Map<String, Object>>) result.get("items"))
                .page(((Number) result.get("page")).longValue())
                .count(((Number) result.get("count")).longValue())
                .build();
    }
    
    /**
     * 构建用户积分查询SQL
     */
    private String buildUserBalanceQuery(String search, int pageSize, int offset) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("    u.id as userId, ");
        sql.append("    u.nickname as userNickname, ");
        sql.append("    u.mobile, ");
        sql.append("    u.real_name as realName, ");
        sql.append("    COALESCE(earned_points.total_earned, 0) as totalEarnedPoints, ");
        sql.append("    COALESCE(used_points.total_used, 0) as totalUsedPoints, ");
        sql.append("    COALESCE(u.points, 0) as pointsBalance, ");
        sql.append("    CASE ");
        sql.append("        WHEN COALESCE(u.points, 0) >= 1000 THEN 'sufficient' ");
        sql.append("        WHEN COALESCE(u.points, 0) > 0 THEN 'insufficient' ");
        sql.append("        ELSE 'zero' ");
        sql.append("    END as balanceStatus, ");
        sql.append("    CASE ");
        sql.append("        WHEN COALESCE(u.points, 0) >= 10000 THEN '积分充足，可上架20个商品（商品价格100-1000元）' ");
        sql.append("        WHEN COALESCE(u.points, 0) >= 5000 THEN '积分良好，可上架10个商品（商品价格100-1000元）' ");
        sql.append("        WHEN COALESCE(u.points, 0) >= 1000 THEN '积分一般，可上架2个商品（商品价格100-1000元）' ");
        sql.append("        WHEN COALESCE(u.points, 0) > 0 THEN '积分较少，可上架1个商品（商品价格100-1000元）' ");
        sql.append("        ELSE '积分不足，无法上架商品' ");
        sql.append("    END as suggestedAction, ");
        sql.append("    'CNY' as currency, ");
        sql.append("    COALESCE(product_stats.total_products, 0) as productCount, ");
        sql.append("    COALESCE(product_stats.online_products, 0) as onlineProductCount, ");
        sql.append("    COALESCE(product_stats.offline_products, 0) as offlineProductCount ");
        sql.append("FROM user u ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as total_earned ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE (type = 'earned' OR type = '1') AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") earned_points ON u.id = earned_points.user_id ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, SUM(points) as total_used ");
        sql.append("    FROM points_record ");
        sql.append("    WHERE (type = 'used' OR type = '2') AND delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") used_points ON u.id = used_points.user_id ");
        sql.append("LEFT JOIN ( ");
        sql.append("    SELECT user_id, ");
        sql.append("           COUNT(*) as total_products, ");
        sql.append("           SUM(CASE WHEN online = 1 THEN 1 ELSE 0 END) as online_products, ");
        sql.append("           SUM(CASE WHEN online = 0 THEN 1 ELSE 0 END) as offline_products ");
        sql.append("    FROM spu ");
        sql.append("    WHERE delete_time IS NULL ");
        sql.append("    GROUP BY user_id ");
        sql.append(") product_stats ON u.id = product_stats.user_id ");
        sql.append("WHERE u.delete_time IS NULL ");
        
        if (search != null && !search.trim().isEmpty()) {
            sql.append("AND (u.nickname LIKE '%").append(search).append("%' OR u.id = '").append(search).append("') ");
        }
        
        sql.append("ORDER BY COALESCE(u.points, 0) DESC ");
        sql.append("LIMIT ").append(pageSize).append(" OFFSET ").append(offset);
        
        return sql.toString();
    }
    
    /**
     * 构建用户余额总数查询SQL
     */
    private String buildUserBalanceCountQuery(String search) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) ");
        sql.append("FROM user u ");
        sql.append("WHERE u.delete_time IS NULL ");
        
        if (search != null && !search.trim().isEmpty()) {
            sql.append("AND (u.nickname LIKE '%").append(search).append("%' OR u.id = '").append(search).append("') ");
        }
        
        return sql.toString();
    }
    
    /**
     * 回退方法：使用原来的查询方式
     */
    private PageResponseVO<Map<String, Object>> getUserBalanceListFallback(Integer page, Integer count, String search) {
        Map<String, Object> result = new HashMap<>();
        
        int pageNum = page != null ? page : 0;
        int pageSize = count != null ? count : 10;
        
        // 获取用户列表
        List<CUserDO> users;
        long total;
        
        if (search != null && !search.trim().isEmpty()) {
            // 搜索用户
            users = cUserService.search(pageNum, pageSize, search).getRecords();
            total = cUserService.search(pageNum, pageSize, search).getTotal();
        } else {
            // 分页获取所有用户
            users = cUserService.getUserByPage(pageSize, pageNum).getRecords();
            total = cUserService.count();
        }
        
        // 计算每个用户的余额
        List<Map<String, Object>> items = new java.util.ArrayList<>();
        for (CUserDO user : users) {
            Map<String, Object> userBalance = new HashMap<>();
            
            // 基本用户信息
            userBalance.put("userId", user.getId());
            userBalance.put("userNickname", user.getNickname());
            userBalance.put("mobile", user.getMobile());
            userBalance.put("realName", user.getRealName());
            
            // 计算用户余额和交易统计
            Map<String, Object> balanceInfo = calculateUserBalanceDetail(user.getId());
            userBalance.put("balance", balanceInfo.get("balance"));
            userBalance.put("balanceStatus", balanceInfo.get("status"));
            userBalance.put("totalPurchase", balanceInfo.get("totalPurchase"));
            userBalance.put("totalSales", balanceInfo.get("totalSales"));
            userBalance.put("currency", "CNY");
            
            // 商品统计（暂时返回0，后续可以实现）
            userBalance.put("productCount", 0);
            userBalance.put("onlineProductCount", 0);
            userBalance.put("offlineProductCount", 0);
            
            // 建议操作
            String suggestedAction = getSuggestedAction((BigDecimal) balanceInfo.get("balance"));
            userBalance.put("suggestedAction", suggestedAction);
            
            items.add(userBalance);
        }
        
        result.put("total", (long) total);
        result.put("count", (long) pageSize);
        result.put("page", (long) pageNum);
        result.put("items", items);
        
        return PageResponseVO.<Map<String, Object>>builder()
                .total(total)
                .items(items)
                .page((long) pageNum)
                .count((long) pageSize)
                .build();
    }

    /**
     * 处理 OPTIONS 预检请求 - 批量自动上架
     */
    @RequestMapping(value = "/batch-auto-listing", method = RequestMethod.OPTIONS)
    public void handleBatchAutoListingOptions() {
        // 空方法，用于处理 CORS 预检请求
    }

    /**
     * 处理 OPTIONS 预检请求 - 统计信息
     */
    @RequestMapping(value = "/stats", method = RequestMethod.OPTIONS)
    public void handleStatsOptions() {
        // 空方法，用于处理 CORS 预检请求
    }

    /**
     * 处理 OPTIONS 预检请求 - 用户余额
     */
    @RequestMapping(value = "/user/*/balance", method = RequestMethod.OPTIONS)
    public void handleUserBalanceOptions() {
        // 空方法，用于处理 CORS 预检请求
    }

    /**
     * 处理 OPTIONS 预检请求 - 用户商品自动上架
     */
    @RequestMapping(value = "/user/*/auto-listing", method = RequestMethod.OPTIONS)
    public void handleUserAutoListingOptions() {
        // 空方法，用于处理 CORS 预检请求
    }

    /**
     * 处理 OPTIONS 预检请求 - 用户余额列表
     */
    @RequestMapping(value = "/user-balances", method = RequestMethod.OPTIONS)
    public void handleUserBalanceListOptions() {
        // 空方法，用于处理 CORS 预检请求
    }

    /**
     * 使用SQL聚合查询计算用户积分分布（高效版本）
     * 一次查询完成所有用户的积分统计
     */
    private Map<String, Object> getPointsStatsBySql() {
        Map<String, Object> result = new HashMap<>();
        
        try {
            // 使用SQL聚合查询一次性计算所有积分统计信息
            String sql = "SELECT " +
                    "SUM(CASE WHEN COALESCE(u.points, 0) >= 1000 THEN 1 ELSE 0 END) as sufficient_points_users, " +
                    "SUM(CASE WHEN COALESCE(u.points, 0) > 0 AND COALESCE(u.points, 0) < 1000 THEN 1 ELSE 0 END) as insufficient_points_users, " +
                    "SUM(CASE WHEN COALESCE(u.points, 0) = 0 OR u.points IS NULL THEN 1 ELSE 0 END) as zero_points_users " +
                    "FROM user u " +
                    "WHERE u.delete_time IS NULL";
            
            Map<String, Object> stats = jdbcTemplate.queryForMap(sql);
            
            result.put("sufficient_points_users", stats.get("sufficient_points_users"));
            result.put("insufficient_points_users", stats.get("insufficient_points_users"));
            result.put("zero_points_users", stats.get("zero_points_users"));
            
            return result;
            
        } catch (Exception e) {
            // 如果SQL查询失败，回退到批量查询方法
            System.err.println("SQL积分统计查询失败，回退到批量查询: " + e.getMessage());
            return getPointsStatsByBatchQuery();
        }
    }
    
    /**
     * 批量查询用户积分分布（优化版本）
     * 减少数据库查询次数
     */
    private Map<String, Object> getPointsStatsByBatchQuery() {
        Map<String, Object> result = new HashMap<>();
        
        // 获取所有用户
        List<CUserDO> allUsers = cUserService.list();
        
        if (allUsers.isEmpty()) {
            result.put("sufficient_points_users", 0);
            result.put("insufficient_points_users", 0);
            result.put("zero_points_users", 0);
            return result;
        }
        
        // 计算积分分布
        long sufficientPointsUsers = 0;
        long insufficientPointsUsers = 0;
        long zeroPointsUsers = 0;
        
        for (CUserDO user : allUsers) {
            Integer points = user.getPoints();
            if (points == null) points = 0;
            
            if (points >= 1000) {
                sufficientPointsUsers++;
            } else if (points > 0) {
                insufficientPointsUsers++;
            } else {
                zeroPointsUsers++;
            }
        }
        
        result.put("sufficient_points_users", sufficientPointsUsers);
        result.put("insufficient_points_users", insufficientPointsUsers);
        result.put("zero_points_users", zeroPointsUsers);
        
        return result;
    }
    
    /**
     * 批量查询用户作为卖家的收入
     */
    private Map<Integer, BigDecimal> getBatchSellerIncomes(List<Integer> userIds) {
        QueryWrapper<OrderDO> query = new QueryWrapper<>();
        query.in("saler", userIds)
             .eq("checked", 1)
             .eq("status", 2);
        
        List<OrderDO> orders = unifiedOrderService.list(query);
        
        return orders.stream()
                .filter(order -> order.getPayAmount() != null)
                .collect(java.util.stream.Collectors.groupingBy(
                    OrderDO::getSaler,
                    java.util.stream.Collectors.reducing(
                        BigDecimal.ZERO,
                        OrderDO::getPayAmount,
                        BigDecimal::add
                    )
                ));
    }
    
    /**
     * 批量查询用户作为买家的支出
     */
    private Map<Integer, BigDecimal> getBatchBuyerExpenses(List<Integer> userIds) {
        QueryWrapper<OrderDO> query = new QueryWrapper<>();
        query.in("user_id", userIds)
             .eq("checked", 1)
             .eq("status", 2);
        
        List<OrderDO> orders = unifiedOrderService.list(query);
        
        return orders.stream()
                .filter(order -> order.getPayAmount() != null)
                .collect(java.util.stream.Collectors.groupingBy(
                    OrderDO::getUserId,
                    java.util.stream.Collectors.reducing(
                        BigDecimal.ZERO,
                        OrderDO::getPayAmount,
                        BigDecimal::add
                    )
                ));
    }

    /**
     * 计算用户余额详情（包含收入和支出统计）
     */
    private Map<String, Object> calculateUserBalanceDetail(Integer userId) {
        Map<String, Object> result = new HashMap<>();
        
        // 查询用户作为卖家的收入（已确认的订单）
        QueryWrapper<OrderDO> sellerQuery = new QueryWrapper<>();
        sellerQuery.eq("saler", userId)
                  .eq("checked", 1) // 已确认的订单
                  .eq("status", 2); // 已支付状态
        
        List<OrderDO> sellerOrders = unifiedOrderService.list(sellerQuery);
        BigDecimal sellerIncome = sellerOrders.stream()
                .map(OrderDO::getPayAmount)
                .filter(amount -> amount != null)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        
        // 查询用户作为买家的支出（已确认的订单）
        QueryWrapper<OrderDO> buyerQuery = new QueryWrapper<>();
        buyerQuery.eq("user_id", userId)
                 .eq("checked", 1) // 已确认的订单
                 .eq("status", 2); // 已支付状态
        
        List<OrderDO> buyerOrders = unifiedOrderService.list(buyerQuery);
        BigDecimal buyerExpense = buyerOrders.stream()
                .map(OrderDO::getPayAmount)
                .filter(amount -> amount != null)
                .reduce(BigDecimal.ZERO, BigDecimal::add);
        
        // 计算余额：收入 - 支出
        BigDecimal balance = sellerIncome.subtract(buyerExpense);
        
        // 确定余额状态
        String status;
        if (balance.compareTo(BigDecimal.ZERO) > 0) {
            status = "positive";
        } else if (balance.compareTo(BigDecimal.ZERO) < 0) {
            status = "negative";
        } else {
            status = "zero";
        }
        
        result.put("balance", balance);
        result.put("status", status);
        result.put("totalSales", sellerIncome); // 作为卖家的收入
        result.put("totalPurchase", buyerExpense); // 作为买家的支出
        
        return result;
    }
    
    /**
     * 根据余额和积分获取建议操作
     */
    private String getSuggestedAction(BigDecimal balance, Integer points) {
        // 优先根据积分判断
        if (points != null && points > 0) {
            int averageProductPrice = 500; // 平均商品价格500元
            int maxListingCount = points / averageProductPrice;
            int maxLimit = 20;
            maxListingCount = Math.min(maxListingCount, maxLimit);
            
            if (maxListingCount == 0) {
                return "积分不足，无法上架商品（需要至少" + averageProductPrice + "积分）";
            } else if (maxListingCount >= 10) {
                return "积分充足，可上架" + maxListingCount + "个商品（商品价格100-1000元）";
            } else if (maxListingCount >= 5) {
                return "积分良好，可上架" + maxListingCount + "个商品（商品价格100-1000元）";
            } else if (maxListingCount >= 2) {
                return "积分一般，可上架" + maxListingCount + "个商品（商品价格100-1000元）";
            } else {
                return "积分较少，可上架" + maxListingCount + "个商品（商品价格100-1000元）";
            }
        }
        
        // 如果没有积分，根据余额判断
        if (balance.compareTo(BigDecimal.ZERO) > 0) {
            return "正余额，建议自动上架";
        } else if (balance.compareTo(BigDecimal.ZERO) < 0) {
            return "负余额，建议自动下架";
        } else {
            return "零余额，建议人工审核";
        }
    }

    /**
     * 根据余额获取建议操作（兼容旧方法）
     */
    private String getSuggestedAction(BigDecimal balance) {
        return getSuggestedAction(balance, null);
    }

    /**
     * 计算用户余额（保留原方法用于单个用户查询）
     * 余额 = 作为卖家的收入 - 作为买家的支出
     */
    private BigDecimal calculateUserBalance(Integer userId) {
        Map<String, Object> balanceInfo = calculateUserBalanceDetail(userId);
        return (BigDecimal) balanceInfo.get("balance");
    }

    /**
     * 获取用户商品列表
     */
    @GetMapping("/user/{userId}/products")
    @CrossOrigin(origins = "*")
    public List<Map<String, Object>> getUserProducts(@PathVariable Integer userId) {
        try {
            Map<String, Object> result = new HashMap<>();
            
            // 查询用户信息
            CUserDO user = cUserService.getById(userId);
            if (user == null) {
                return new ArrayList<>();
            }
            
            // 查询用户的所有商品
            QueryWrapper<SpuDO> spuQuery = new QueryWrapper<>();
            spuQuery.eq("user_id", userId)
                   .isNull("delete_time")
                   .orderByDesc("create_time");
            
            List<SpuDO> spuList = spuService.list(spuQuery);
            
            // 使用SQL聚合查询优化性能，避免N+1问题
            List<Map<String, Object>> productList = getUserProductsBySql(userId, spuList);
            
            result.put("userId", userId);
            result.put("userNickname", user.getNickname());
            result.put("userMobile", user.getMobile());
            result.put("userRealName", user.getRealName());
            result.put("userPoints", user.getPoints());
            result.put("products", productList);
            result.put("totalProducts", spuList.size());
            result.put("totalSkus", spuList.stream().mapToInt(spu -> {
                QueryWrapper<SkuDO> skuQuery = new QueryWrapper<>();
                skuQuery.eq("spu_id", spu.getId()).isNull("delete_time");
                return skuService.count(skuQuery);
            }).sum());
            result.put("onlineSkus", spuList.stream().mapToInt(spu -> {
                QueryWrapper<SkuDO> skuQuery = new QueryWrapper<>();
                skuQuery.eq("spu_id", spu.getId()).eq("online", 1).isNull("delete_time");
                return skuService.count(skuQuery);
            }).sum());
            result.put("offlineSkus", spuList.stream().mapToInt(spu -> {
                QueryWrapper<SkuDO> skuQuery = new QueryWrapper<>();
                skuQuery.eq("spu_id", spu.getId()).eq("online", 0).isNull("delete_time");
                return skuService.count(skuQuery);
            }).sum());
            
            // 返回商品列表
            return (List<Map<String, Object>>) result.get("products");
        } catch (Exception e) {
            logger.error("获取用户商品列表失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 使用SQL聚合查询获取用户商品列表，优化性能
     */
    private List<Map<String, Object>> getUserProductsBySql(Integer userId, List<SpuDO> spuList) {
        if (spuList.isEmpty()) {
            return new ArrayList<>();
        }
        
        // 构建SPU ID列表
        List<Integer> spuIds = spuList.stream().map(SpuDO::getId).collect(java.util.stream.Collectors.toList());
        String spuIdList = spuIds.stream().map(String::valueOf).collect(java.util.stream.Collectors.joining(","));
        
        // 使用SQL聚合查询获取所有SKU信息
        String sql = "SELECT " +
                    "    s.id as spu_id, " +
                    "    s.title as spu_title, " +
                    "    s.subtitle as spu_subtitle, " +
                    "    s.img as spu_img, " +
                    "    s.price as spu_price, " +
                    "    s.online as spu_online, " +
                    "    s.create_time as spu_create_time, " +
                    "    sk.id as sku_id, " +
                    "    sk.title as sku_title, " +
                    "    sk.img as sku_img, " +
                    "    sk.price as sku_price, " +
                    "    sk.discount_price as sku_discount_price, " +
                    "    sk.online as sku_online, " +
                    "    sk.code as sku_code, " +
                    "    sk.stock as sku_stock, " +
                    "    sk.specs as sku_specs " +
                    "FROM spu s " +
                    "LEFT JOIN sku sk ON s.id = sk.spu_id AND sk.delete_time IS NULL " +
                    "WHERE s.id IN (" + spuIdList + ") " +
                    "ORDER BY s.create_time DESC, sk.price ASC";
        
        List<Map<String, Object>> rawResults = jdbcTemplate.queryForList(sql);
        
        // 按SPU分组处理结果
        Map<Integer, Map<String, Object>> productMap = new HashMap<>();
        
        for (Map<String, Object> row : rawResults) {
            Object spuIdObj = row.get("spu_id");
            Integer spuId = null;
            if (spuIdObj instanceof Long) {
                spuId = ((Long) spuIdObj).intValue();
            } else if (spuIdObj instanceof Integer) {
                spuId = (Integer) spuIdObj;
            } else if (spuIdObj instanceof Number) {
                spuId = ((Number) spuIdObj).intValue();
            }
            
            if (spuId == null) continue;
            
            // 初始化SPU信息
            if (!productMap.containsKey(spuId)) {
                Map<String, Object> productInfo = new HashMap<>();
                productInfo.put("spuId", spuId);
                productInfo.put("spuTitle", row.get("spu_title"));
                productInfo.put("spuSubtitle", row.get("spu_subtitle"));
                productInfo.put("spuImg", row.get("spu_img"));
                productInfo.put("spuPrice", row.get("spu_price"));
                productInfo.put("spuOnline", row.get("spu_online"));
                productInfo.put("spuCreateTime", row.get("spu_create_time"));
                productInfo.put("skus", new ArrayList<Map<String, Object>>());
                productInfo.put("skuCount", 0);
                productInfo.put("onlineSkuCount", 0);
                productInfo.put("offlineSkuCount", 0);
                productMap.put(spuId, productInfo);
            }
            
            // 添加SKU信息（如果存在）
            Object skuIdObj = row.get("sku_id");
            if (skuIdObj != null) {
                Map<String, Object> skuInfo = new HashMap<>();
                skuInfo.put("skuId", skuIdObj);
                skuInfo.put("skuTitle", row.get("sku_title"));
                skuInfo.put("skuImg", row.get("sku_img"));
                skuInfo.put("skuPrice", row.get("sku_price"));
                skuInfo.put("skuDiscountPrice", row.get("sku_discount_price"));
                skuInfo.put("skuOnline", row.get("sku_online"));
                skuInfo.put("skuCode", row.get("sku_code"));
                skuInfo.put("skuStock", row.get("sku_stock"));
                skuInfo.put("skuSpecs", row.get("sku_specs"));
                
                @SuppressWarnings("unchecked")
                List<Map<String, Object>> skuList = (List<Map<String, Object>>) productMap.get(spuId).get("skus");
                skuList.add(skuInfo);
                
                // 更新统计信息
                Integer skuCount = (Integer) productMap.get(spuId).get("skuCount");
                productMap.get(spuId).put("skuCount", skuCount + 1);
                
                Object skuOnlineObj = row.get("sku_online");
                if (skuOnlineObj != null) {
                    Integer skuOnline = (Integer) skuOnlineObj;
                    if (skuOnline == 1) {
                        Integer onlineCount = (Integer) productMap.get(spuId).get("onlineSkuCount");
                        productMap.get(spuId).put("onlineSkuCount", onlineCount + 1);
                    } else {
                        Integer offlineCount = (Integer) productMap.get(spuId).get("offlineSkuCount");
                        productMap.get(spuId).put("offlineSkuCount", offlineCount + 1);
                    }
                } else {
                    Integer offlineCount = (Integer) productMap.get(spuId).get("offlineSkuCount");
                    productMap.get(spuId).put("offlineSkuCount", offlineCount + 1);
                }
            }
        }
        
        return new ArrayList<>(productMap.values());
    }

    /**
     * 处理OPTIONS请求 - 用户商品列表
     */
    @RequestMapping(value = "/user/{userId}/products", method = RequestMethod.OPTIONS)
    @CrossOrigin(origins = "*")
    public ResponseEntity<Void> handleUserProductsOptions(@PathVariable Integer userId) {
        return ResponseEntity.ok().build();
    }
}
